#! python3
#Author:Heanny Liu
#Use:BGP
#Date:2017-7-1
import pymysql, json, time, operator, threading, telnetlib, os, sys
from apscheduler.schedulers.blocking import BlockingScheduler
from apscheduler.schedulers.background import BackgroundScheduler
from datetime import datetime

_print = print
def print(msg):
	_print('{} >> {}'.format(time.strftime('%Y-%m-%d %H:%M:%S'),msg))

class telClass():
	ip = '127.0.0.1'
	port = 2605
	pwd = 'zebra'
	timeout = 50


class SqlService():
	def __init__(self):
		self.host = 'localhost'
		self.user = 'root'
		self.pwd = 'www.456.com'
		self.database = 'gm'
		self.db = None
		print('__init__ success')

	def _Link(self):
		try:
			'''连接数据库，并创建新表格，获取数据'''
			self.db = pymysql.connect(self.host, self.user, self.pwd, self.database)
			self.db.set_charset('utf8')
			# self.db.set_charset('utf-8')
			self.cursor = self.db.cursor()
			try:
				self.cursor.execute("TRUNCATE out0624;")
				self.db.commit()
				self.cursor.execute("DROP TABLE out0624;")
				self.db.commit()
			except Exception as e:
				print(e)
				pass
			self.cursor.execute(
				"CREATE TABLE out0624 SELECT gn,gi,GROUP_CONCAT(go SEPARATOR' ') AS go FROM ng WHERE gn!='testing' AND gi IN (SELECT ip FROM findipnua WHERE pd1 IS NOT NULL) GROUP BY gi,gn HAVING COUNT(DISTINCT(gn))=1;")
			self.db.commit()
			print('CREATE TABLE out0624')
			try:
				self.cursor.execute("TRUNCATE hout0624; ")
				self.db.commit()
				self.cursor.execute("DROP TABLE hout0624; ")
				self.db.commit()
			except Exception as e:
				print(e)
			self.cursor.execute(
				"CREATE TABLE hout0624 SELECT gn,gi,go,pd1,pd2,pd3 FROM out0624 LEFT JOIN findipnua ON gi=ip;")
			self.db.commit()
			print('CREATE TABLE hout0624')
		except Exception as e:
			print(e)

	def _Query(self, com):
		try:
			'''获取命令行数据'''
			self.cursor = self.db.cursor()
			self.cursor.execute(com)
			datas = self.cursor.fetchall()
			print('get data success')
			return datas
		except Exception as e:
			print(e)
			return False

	def _Close(self):
		try:
			self.db.commit()
			self.db.close()
			print('tn is closed')
			return True
		except Exception as e:
			print(e)
			return False

	def _Save(self, data):
		try:
			'''将本次数据写到历史文件，方便下次比较'''
			f = open('oldData.json', 'w+', encoding='utf-8')
			f.write('{}\n'.format(json.dumps(data)))
			f.close()
			return True
		except Exception as e:
			return False

	def _DataContrast(self, newData):
		try:
			'''获取历史数据并进行比较'''
			f = open('oldData.json', encoding='utf-8')
			datas = json.loads(f.read().replace('\n', ''))
			f.close()
			addList = list(set(newData).difference(set(datas)))
			delList = list(set(datas).difference(set(newData)))
			return {'add': addList, 'del': delList, 'new': newData, 'old': datas}
		except Exception as e:
			print(e)
			return False


def TelnetLib(data):
	try:
		'''telnet本地，并进行bgp路由同步'''
		tn = telnetlib.Telnet(telClass.ip, port=telClass.port, timeout=telClass.timeout)  # 23
		print('tn:{}'.format(tn))
		tn.read_until('Password:'.encode())
		tn.write('zebra\n'.encode())
		print('input pwd end')
		time.sleep(1)
		tn.read_until('bgpd>'.encode())
		print('login success')
		tn.write('ena\n'.encode())
		time.sleep(1)
		tn.write('configure terminal\n'.encode())
		time.sleep(1)
		tn.write('router bgp 131481\n'.encode())
		# add
		for addData in data['add']:
			time.sleep(0.5)
			print('network {}/32\n'.format(addData))
			tn.write('network {}/32\n'.format(addData).encode())
			print('add new ip {} success'.format(addData))
		# del
		for delData in data['del']:
			time.sleep(1)
			print('no network {}/32\n'.format(delData))
			tn.write('no network {}/32\n'.format(delData).encode())
			print('delete ip {} success'.format(delData))
		result = tn.read_very_eager()
		print('result :{}'.format(result.decode()))
		tn.write('q\n'.encode())
		tn.write('q\n'.encode())
		tn.close()
		tn =None
		return True
	except Exception as e:
		print(e)
		try:
			tn.close()
		except Exception as e:
			print(e)
			pass
		tn = None
		return False


def main():
	SS = SqlService()
	'''主函数'''
	SS._Link()
	datas = list(map(lambda x: x[0], SS._Query('SELECT gi FROM hout0624;')))
	SS._Close()
	# SS._Save(datas)
	ReData = SS._DataContrast(datas)
	SS._Save(datas)
	# '{}>>{}\n'.format(time.strftime('%Y-%m-%d %H:%M:%S')
	ShowLog('{} >> add : {} \n{} >> del : {}\n'.format(time.strftime('%Y-%m-%d %H:%M:%S'), json.dumps(ReData['add']),
	                                                   time.strftime('%Y-%m-%d %H:%M:%S'), json.dumps(ReData['del'])))
	print('all : {}'.format(len(list(set(ReData['new'])))))
	if len(ReData['new'])!=0:
		if tn is None:
			if len(ReData['add'])!=0 or len(ReData['del'])!=0:
				t = threading.Thread(target=TelnetLib, args=(ReData,))
				t.start()
				t.join()
			else:
				print('no change')
		else:
			print('t is running')
	else:
		print('data error')
	print('t end ')
	return True


def blockMain():
	scheduler = BlockingScheduler()
	'''当调度程序是您的进程中唯一运行的时候使用'''
	scheduler.add_job(main, 'cron', minute    ='*/10')
	print('Press Ctrl+{0} to exit'.format('Break' if os.name == 'nt' else 'C'))
	try:
		scheduler.start()
	except (KeyboardInterrupt, SystemExit):
		scheduler.shutdown()


def ShowLog(data):
	f = open('log.txt', 'a+', encoding='utf-8')
	'''输出日志'''
	f.write(data)
	print('log success')
	f.close()


if __name__ == '__main__':
	# f = open('ll.json')
	# fData = json.loads(f.read().replace('\n', ''))
	# f.close()
	# print(type(fData))
	# print(fData)
	version = 'V1.2.18_7_3'
	print('bgp service {}'.format(version))
	global tn
	tn =None
	main()
	blockMain()
